const fs = require('fs');
const path = require('path');
const { pool } = require('./config/db');

// First find existing constraints
const findConstraintSQL = `
SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'adoption_applications' 
AND COLUMN_NAME = 'pet_id' 
AND REFERENCED_TABLE_NAME = 'pets'
`;

// Also find if our constraint name already exists
const findDuplicateConstraintSQL = `
SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'adoption_applications' 
AND CONSTRAINT_NAME = 'adoption_applications_pet_id_fk_cascade'
`;

async function applyFixes() {
  console.log('Applying database fixes...');
  
  try {
    // Get a connection from the pool
    const connection = await pool.getConnection();
    
    console.log('Connected to database');
    
    // Check if our intended constraint name already exists
    console.log('Checking for duplicate constraint names...');
    const [duplicates] = await connection.query(findDuplicateConstraintSQL);
    
    // Create a unique constraint name
    const constraintName = duplicates.length > 0 
      ? `adoption_applications_pet_id_fk_cascade_${Date.now()}`
      : 'adoption_applications_pet_id_fk_cascade';
    
    console.log(`Using constraint name: ${constraintName}`);
    
    // Find existing constraints
    console.log('Finding existing foreign key constraints...');
    const [constraints] = await connection.query(findConstraintSQL);
    
    if (constraints.length > 0) {
      for (const constraint of constraints) {
        console.log(`Found existing constraint: ${constraint.CONSTRAINT_NAME}`);
        
        // Drop existing constraint
        const dropConstraintSQL = `
          ALTER TABLE adoption_applications 
          DROP FOREIGN KEY ${constraint.CONSTRAINT_NAME}
        `;
        
        console.log(`Dropping constraint: ${constraint.CONSTRAINT_NAME}...`);
        await connection.query(dropConstraintSQL);
        console.log('Constraint dropped successfully');
      }
    } else {
      console.log('No existing constraint found for pet_id column');
    }
    
    // Add new constraint with CASCADE DELETE
    const addConstraintSQL = `
      ALTER TABLE adoption_applications
      ADD CONSTRAINT ${constraintName}
      FOREIGN KEY (pet_id) REFERENCES pets(id)
      ON DELETE CASCADE
    `;
    
    console.log('Adding new constraint with ON DELETE CASCADE...');
    await connection.query(addConstraintSQL);
    console.log('Constraint added successfully');
    
    // Verify the constraint
    console.log('Verifying constraint...');
    const [verifyResult] = await connection.query('SHOW CREATE TABLE adoption_applications');
    console.log('Table structure:');
    console.log(verifyResult[0]['Create Table']);
    
    // Release the connection back to the pool
    connection.release();
    
    // Close the pool
    await pool.end();
    
    console.log('Database connection closed');
    console.log('Database fixes applied successfully');
  } catch (error) {
    console.error('Error applying database fixes:', error);
    process.exit(1);
  }
}

// Run the script
applyFixes(); 